#define _GNU_SOURCE
#include <dirent.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <string.h>
#include <stdio.h>
#include <unistd.h>
#include <stdbool.h>
#include <stdlib.h>
#include <time.h>
#include "sqlite3.h"
#include "dataHandle.h"
#include "xml_conf.h"
#include "cli.h"
#include "ftpUpload.h"
const char* sql_begin = "begin;";
const char* sql_commit = "commit;";
const char* sql_rollback = "rollback;";
const char* dbPath = "/mnt/usr/fepRecord";
const char* dbName = "fepDB";
const char* fepDbPath = "/mnt/usr/fepRecord/fepDB";
const char* sql_creatdb = "PRAGMA synchronous = NORMAL;PRAGMA temp_store = MEMORY; CREATE TABLE IF NOT EXISTS PHONE_INFO( \
        ID INTERGER, \
        TIMESTAMP INTEGER , \
        IMSI VARCHAR(16), \
        IMEI VARCHAR(16), \
        SLOTID INTEGER);";

const char* sql_creatMacdb = "PRAGMA synchronous = NORMAL;PRAGMA temp_store = MEMORY; CREATE TABLE IF NOT EXISTS MAC_INFO( \
        ID INTERGER, \
        TIMESTAMP INTEGER , \
        MAC VARCHAR(12));";

const char* sql_creatidx= "CREATE INDEX IF NOT EXISTS TS_INDEX ON PHONE_INFO(ID);";  
const char* sql_creatMacidx = "CREATE INDEX IF NOT EXISTS TS_INDEX ON MAC_INFO(ID);";
const char* sql_selectMax = "SELECT max(ID) FROM PHONE_INFO;";
const char* sql_selectMin = "SELECT min(ID) FROM PHONE_INFO;";
const char* sql_selectMacMax = "SELECT max(ID) FROM MAC_INFO;";
const char* sql_selectMacMin = "SELECT min(ID) FROM MAC_INFO;";

const char* sql_creatUserdb = "PRAGMA synchronous = NORMAL;PRAGMA temp_store = MEMORY; CREATE TABLE IF NOT EXISTS USER_INFO( \
        TIMESTAMP INTEGER PRIMARY KEY, \
        TARGETIMSI VARCHAR(512), \
        USERNAME VARCHAR(24), \
        USERID VARCHAR(24), \
        CATCHIMSI VARCHAR(128));";


const char* sql_creatUseridx= "CREATE INDEX IF NOT EXISTS TS_INDEX ON USER_INFO(TIMESTAMP);";  
const char* sql_selectUserMax = "SELECT max(ID) FROM USER_INFO;";
const char* sql_selectUserMin = "SELECT min(ID) FROM USER_INFO;";


static sqlite3* RecordDb = NULL;
static sqlite3* currentDB = NULL;
static U32 coutSendId = 0;

typedef struct tagPHONEINFOCTR
{
    char **pResult;
    I32 nRow;
    I32 nCol;
    char date[12];
    U32 currentTm;
    I8 sqlCmd[512];
} PHONEINFOCTR;

PHONEINFOCTR phoneInfoCtr;

bool initsqlite()
{
    DIR *pDir = NULL;
    pDir = opendir(dbPath);
    I32 rc = 0;
    if ( pDir == NULL )
    {
        if ( mkdir(dbPath, 0777) != 0 )
        {
            cli_app_log(CLI_LOG_ERROR, "can not create dir %s \n", dbPath);
            return false;
        }

    }
    else
    {
        closedir(pDir);
    }

    rc = sqlite3_open(fepDbPath, &RecordDb);
    
    if ( SQLITE_OK != rc)
    {
        cli_app_log(CLI_LOG_ERROR, "can not open db %s \n", fepDbPath);
        return false;        
    }
    
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        return false;
    }

    
    rc = sqlite3_exec(RecordDb, sql_creatdb, 0, 0, NULL);
    if ( SQLITE_OK != rc )
    {
        cli_app_log(CLI_LOG_ERROR, "can not create table  %d \n", rc);
        sqlite3_close(RecordDb);
        return false;
    }

    rc = sqlite3_exec(RecordDb, sql_creatidx, 0, 0, NULL);
    if ( SQLITE_OK != rc )
    {
        cli_app_log(CLI_LOG_ERROR, "can not create idx %d \n", rc);
        sqlite3_close(RecordDb);
        return false;
    }    

    
    rc = sqlite3_exec(RecordDb, sql_creatMacdb, 0, 0, NULL);
    if ( SQLITE_OK != rc )
    {
        cli_app_log(CLI_LOG_ERROR, "can not create table  %d \n", rc);
        sqlite3_close(RecordDb);
        return false;
    }

    rc = sqlite3_exec(RecordDb, sql_creatMacidx, 0, 0, NULL);
    if ( SQLITE_OK != rc )
    {
        cli_app_log(CLI_LOG_ERROR, "can not create idx %d \n", rc);
        sqlite3_close(RecordDb);
        return false;
    }    
    
    return true;
}

void getRecordMaxIdAndMinId(I32* maxId, I32* minId , I32 type)
{
    I32 rc = 0;
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "Unexpected error in getRecord \n");
        return ;
    }
    
    char **pResult;
    I32 nRow;
    I32 nCol;
     
    if ( type == DATA_TYPE_IMSI )   
        rc = sqlite3_get_table(RecordDb, sql_selectMax, &pResult, &nRow, &nCol, NULL) ;
    else
        rc = sqlite3_get_table(RecordDb, sql_selectMacMax, &pResult, &nRow, &nCol, NULL) ;   
    if ( rc !=  SQLITE_OK )
    {
        sqlite3_free_table(pResult);
        cli_app_log(CLI_LOG_ERROR, "can not get max Id %x \n", rc);
        sqlite3_close(RecordDb);
        initsqlite(); //here to open db again
        return ;
    }
    cli_app_log(CLI_LOG_INFO, "result is %s nRows is %d \n", pResult[0], nRow);
    if ( nRow == 1 && pResult[1] != NULL )
    {    
        
        *maxId = atoi(pResult[1]);
        sqlite3_free_table(pResult);
        pResult = NULL;
    }

    if ( type == DATA_TYPE_IMSI ) 
        rc = sqlite3_get_table(RecordDb, sql_selectMin, &pResult, &nRow, &nCol, NULL);
    else
        rc = sqlite3_get_table(RecordDb, sql_selectMacMin, &pResult, &nRow, &nCol, NULL);  
    if ( rc !=  SQLITE_OK )
    {
        sqlite3_free_table(pResult);
        cli_app_log(CLI_LOG_ERROR, "can not get min Id %x \n", rc);
        sqlite3_close(RecordDb);
        initsqlite(); //here to open db again
        return ;
    }
    
    if ( nRow == 1 && pResult[1] != NULL )
    {    
        *minId = atoi(pResult[nCol]);
        sqlite3_free_table(pResult);
        pResult = NULL;
    }    
    
}

bool savePhoneInfo(I32 id, U32 uptime, I8 *imsi, I8 *imei, I32 slotId)
{
    I32 rc = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    sprintf(phoneInfoCtr.sqlCmd, "INSERT INTO PHONE_INFO VALUES(%d, %d, '%s', '%s', %d);", 
            id, uptime, 
            (imsi == NULL?"NULL":imsi),
            (imei == NULL?"NULL":imei),  
             slotId);
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }

    rc = sqlite3_exec(RecordDb, phoneInfoCtr.sqlCmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "Insert phone Info failed cmd is %s failedCode %d" , phoneInfoCtr.sqlCmd, rc);
        return false;
    }
    cli_app_log(CLI_LOG_DEFAULT, "save phone success id is %d imsi is %s \n", id, imsi);
    return true;
}

bool saveMacInfo(I32 id, U32 uptime, I8 *mac)
{
    I32 rc = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    sprintf(phoneInfoCtr.sqlCmd, "INSERT INTO MAC_INFO VALUES(%d, %d, '%s');", 
            id, uptime, 
            (mac == NULL?"NULL":mac));
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }

    rc = sqlite3_exec(RecordDb, phoneInfoCtr.sqlCmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "Insert phone Info failed cmd is %s failedCode %d" , phoneInfoCtr.sqlCmd, rc);
        return false;
    }
    cli_app_log(CLI_LOG_DEFAULT, "save phone success id is %d mac is %s \n", id, mac);
    return true;

}

void getDbRecord(bool oldData, RECORDINFO *recordInfo, I32 *startIndex, I32 *endIndex, bool intervalUse)
{
    I32 maxRecordNum = 0;
    if (strcmp(dev_conf.dev.protocol, LEGU) == 0)
    {
        maxRecordNum = 50;
    }
    else if (strcmp(dev_conf.dev.protocol, ZHONGDUN) == 0)
    {
        maxRecordNum = 100;
    }
    else
    {
        maxRecordNum = MAX_RECORD_NUM;
    }
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }

    I32 getId = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    if ( oldData )
    {
        getId = (recordInfo->oldId + maxRecordNum);
        sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM PHONE_INFO WHERE ID >= %d AND ID < %d;", recordInfo->minId, getId);
        if ( !intervalUse )
            *startIndex = recordInfo->minId;
    }
    else
    {
        if ( recordInfo->maxId > (recordInfo->lastId + maxRecordNum) )
        {
            getId = (recordInfo->maxId - maxRecordNum);
        }
        else
        {
            getId = recordInfo->lastId;
      
        }
        sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM PHONE_INFO WHERE ID >= %d AND ID <= %d;", getId, recordInfo->maxId);
    }

    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return;
    }

    if ( !oldData )
    {
        *startIndex = getId;
        *endIndex = recordInfo->maxId;
        recordInfo->lastId = recordInfo->maxId + 1;

    }
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > maxRecordNum)
    {
        recordNum = maxRecordNum;
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    I32 id = 0;
    for ( i = 0; i < recordNum; i++ )
    {
        if ( recordInfo->realNum == maxRecordNum )
            break;
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL || dbResult[4] == NULL || dbResult[0] == NULL || dbResult[2] == NULL)
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        recordInfo->phoneInfoBuf[recordInfo->realNum].uptime = atoi(dbResult[1]);
        strncpy(recordInfo->phoneInfoBuf[recordInfo->realNum].imsi, (dbResult[2] == NULL?nchar:dbResult[2]), 16);
        strncpy(recordInfo->phoneInfoBuf[recordInfo->realNum].imei, (dbResult[3] == NULL?nchar:dbResult[3]), 16);
        recordInfo->phoneInfoBuf[recordInfo->realNum++].slotId = atoi(dbResult[4]);
        id = atoi(dbResult[0]);
        coutSendId++;
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d imsi is %s  countSendIMSI is %d\n", atoi(dbResult[0]), recordInfo->phoneInfoBuf[recordInfo->realNum - 1].imsi, coutSendId);
    }
    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }
    if ( oldData )
    {        
        recordInfo->oldId = getId;
        if ( getId > recordInfo->maxId )
        {
            recordInfo->minId = recordInfo->maxId;
        }
        if (id != 0 )
        {

            *endIndex = id;
            recordInfo->minId = (id + 1);
        }
        else
        {
            if ( recordInfo->oldId >  recordInfo->maxId )
            {
                recordInfo->minId = recordInfo->maxId;
            }
            else
            {
                recordInfo->minId = recordInfo->oldId;
            }
            cli_app_log(CLI_LOG_WARNING, "get record is in %d sendCountId is %d  \n", recordInfo->minId, coutSendId);
        }
 
        if ( recordInfo->realNum < maxRecordNum  && getId  < recordInfo->maxId )
        {            
            getDbRecord(true, recordInfo, startIndex, endIndex, true);  
        }
    }
    cli_app_log(CLI_LOG_INFO, "start Index is %d end Index is %d \n", *startIndex, *endIndex);
}

void getUploadFailedRecord(RECORDINFO *recordInfo, I32 startIndex, I32 endIndex)
{
    recordInfo->realNum = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM PHONE_INFO WHERE ID >= %d AND ID <= %d;", startIndex, endIndex);
    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return;
    }    
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > MAX_RECORD_NUM)
    {
        recordNum = MAX_RECORD_NUM;
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    for ( i = 0; i < recordNum; i++ )
    {
        if ( recordInfo->realNum == MAX_RECORD_NUM )
            break;
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL || dbResult[4] == NULL || dbResult[0] == NULL )
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        recordInfo->phoneInfoBuf[recordInfo->realNum].uptime = atoi(dbResult[1]);
        strncpy(recordInfo->phoneInfoBuf[recordInfo->realNum].imsi, (dbResult[2] == NULL?nchar:dbResult[2]), 16);
        strncpy(recordInfo->phoneInfoBuf[recordInfo->realNum].imei, (dbResult[3] == NULL?nchar:dbResult[3]), 16);
        recordInfo->phoneInfoBuf[recordInfo->realNum++].slotId = atoi(dbResult[4]);
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d imsi is %s \n", atoi(dbResult[0]), recordInfo->phoneInfoBuf[recordInfo->realNum - 1].imsi);
    }
    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }

}

void getDbMacRecord(bool oldData, MACRECORDINFO *recordInfo, I32 *startIndex, I32 *endIndex, bool intervalUse)
{
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }

    I32 getId = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    if ( oldData )
    {
        getId = (recordInfo->oldId + MAX_RECORD_NUM);
        sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM MAC_INFO WHERE ID >= %d AND ID < %d;", recordInfo->minId, getId);
        if ( !intervalUse )
            *startIndex = recordInfo->minId;
    }
    else
    {
        if ( recordInfo->maxId > (recordInfo->lastId + MAX_RECORD_NUM) )
        {
            getId = (recordInfo->maxId - MAX_RECORD_NUM);
        }
        else
        {
            getId = recordInfo->lastId;
      
        }
        sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM MAC_INFO WHERE ID >= %d AND ID <= %d;", getId, recordInfo->maxId);
    }

    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return;
    }

    if ( !oldData )
    {
        *startIndex = getId;
        *endIndex = recordInfo->maxId;
        recordInfo->lastId = recordInfo->maxId + 1;

    }
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > MAX_RECORD_NUM)
    {
        recordNum = MAX_RECORD_NUM;
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    I32 id = 0;
    for ( i = 0; i < recordNum; i++ )
    {
        if ( recordInfo->realNum == MAX_RECORD_NUM )
            break;
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL ||  dbResult[0] == NULL || dbResult[2] == NULL)
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        recordInfo->macInfoBuf[recordInfo->realNum].uptime = atoi(dbResult[1]);
        strncpy(recordInfo->macInfoBuf[recordInfo->realNum++].mac, (dbResult[2] == NULL?nchar:dbResult[2]), 14);
        id = atoi(dbResult[0]);
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d mac is %s \n", atoi(dbResult[0]), recordInfo->macInfoBuf[recordInfo->realNum - 1].mac);
    }
    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }
    if ( oldData )
    {        
        recordInfo->oldId = getId;
        if ( getId > recordInfo->maxId )
        {
            recordInfo->minId = recordInfo->maxId;
        }
        if (id != 0 )
        {

            *endIndex = id ;
            recordInfo->minId = (id + 1);
        }
        else
        {
            if ( recordInfo->oldId >  recordInfo->maxId )
            {
                recordInfo->minId = recordInfo->maxId;
            }
            else
            {
                recordInfo->minId = recordInfo->oldId;
            }
            cli_app_log(CLI_LOG_WARNING, "get record is in %d  \n", recordInfo->minId);
        }
 
        if ( recordInfo->realNum < MAX_RECORD_NUM  && getId  < recordInfo->maxId )
        {            
            getDbMacRecord(true, recordInfo, startIndex, endIndex, true);  
        }
    }
    cli_app_log(CLI_LOG_INFO, "start Index is %d end Index is %d \n", *startIndex, *endIndex);
}

void getUploadMacFailedRecord(MACRECORDINFO *recordInfo, I32 startIndex, I32 endIndex)
{
    recordInfo->realNum = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM MAC_INFO WHERE ID >= %d AND ID <= %d;", startIndex, endIndex);
    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return;
    }    
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > MAX_RECORD_NUM)
    {
        recordNum = MAX_RECORD_NUM;
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    for ( i = 0; i < recordNum; i++ )
    {
        if ( recordInfo->realNum == MAX_RECORD_NUM )
            break;
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL ||  dbResult[0] == NULL || dbResult[2] == NULL)
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        recordInfo->macInfoBuf[recordInfo->realNum].uptime = atoi(dbResult[1]);
        strncpy(recordInfo->macInfoBuf[recordInfo->realNum++].mac, (dbResult[2] == NULL?nchar:dbResult[2]), 14);
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d mac is %s \n", atoi(dbResult[0]), recordInfo->macInfoBuf[recordInfo->realNum - 1].mac);
    }
    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }

}



void delDbRecord(I32 startIndex, I32 endIndex, I32 type)
{
    memset(phoneInfoCtr.sqlCmd, 0, 512); 
    if ( type == DATA_TYPE_IMSI )
    {
        cli_app_log(CLI_LOG_INFO, "del imsi from where id >= %d  bigger than <= %d\n", startIndex, endIndex);          
        sprintf(phoneInfoCtr.sqlCmd, "DELETE FROM PHONE_INFO WHERE ID >= %d AND ID <= %d;", startIndex, endIndex);
    }
    else
    {
        cli_app_log(CLI_LOG_INFO, "del mac from where id >= %d  bigger <= %d\n", startIndex, endIndex);          
        sprintf(phoneInfoCtr.sqlCmd, "DELETE FROM MAC_INFO WHERE ID >= %d AND ID <= %d;", startIndex, endIndex);
    }
    I32 rc = sqlite3_exec(RecordDb, phoneInfoCtr.sqlCmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "dele phone Info failed cmd is %s failedCode %d" , phoneInfoCtr.sqlCmd, rc);
        return ;
    } 

}

void sqliteStartTransaction()
{
    I32 rc = sqlite3_exec(RecordDb, sql_begin, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "start phone Info failed cmd is %s failedCode %d" , sql_begin, rc);
        return ;
    }    
}

bool sqliteCommitTransaction()
{
    I32 rc = sqlite3_exec(RecordDb, sql_commit, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "commit phone Info failed cmd is %s failedCode %d" , sql_begin, rc);
        sqlite3_exec(RecordDb, sql_rollback, 0, 0, NULL);
        return false;
    } 
    return true;
}

bool rmDbAndinitSqlite()
{
    if ( RecordDb != NULL )
    {
        sqlite3_close(RecordDb);
        memset(phoneInfoCtr.sqlCmd, 0, 512);
        sprintf(phoneInfoCtr.sqlCmd, "rm  %s", fepDbPath);
        system(phoneInfoCtr.sqlCmd);
        initsqlite();
    }
    return true;
}

bool saveMacToFtpFileOrSendByUdp(const char *filename, I32 *rows, I32 *minId, I32 *maxId)
{
    if (*maxId == 0)
    {
        return false;
    }
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }
    FILE *fd = NULL;

    if (strcmp(dev_conf.dev.protocol, BAIZHUO) != 0)
    {
        fd = fopen(filename, "a");
        if (fd == NULL)
        {
            return false;        
        }
    }
    I32 getId = 0, currentId = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    getId = *minId + atoi(dev_conf.ftp.ftpMacmaxcount);
    sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM MAC_INFO WHERE ID >= %d AND ID < %d;", *minId, getId);
    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return false;
    }    
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > atoi(dev_conf.ftp.ftpMacmaxcount))
    {
        recordNum = atoi(dev_conf.ftp.ftpMacmaxcount);
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    I32 id = 0, j = 0;
    U32 uptime = 0;
    char mac[20] = {0};
    char szbuf[64] = {0};
    S8 strength = getField_Strength();
    for ( i = 0; i < recordNum; i++ )
    {
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL ||  dbResult[0] == NULL || dbResult[2] == NULL)
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        uptime = atoi(dbResult[1]);
        memset(szbuf, 0, 64);
        strftime(szbuf, 64, "%Y-%m-%d %H:%M:%S", localtime(((time_t *)(&uptime))));
        memset(mac, 0, 20);
        for (j = 0; j < 6; j++)
        {
            if (j != 0)
            {
                mac[2 * j + j - 1] = '-';
            }
            memcpy(mac + (j * 3) , dbResult[2] +(j *2), 2);
        }
        if (strcmp(dev_conf.dev.protocol, BAIZHUO) != 0)
        {
                fprintf(fd, "%s\t%s\r\n", mac, szbuf);
        }
        else
        {
            sendMacDataByUdp(mac, szbuf);
        }
        id = atoi(dbResult[0]);
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d mac is %s  %s\n", atoi(dbResult[0]), mac, dbResult[2] );
    }

    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    if (id > 0)
    {
        *minId = id + 1;
    }
    else
    {
        *minId = getId;
    }

    cli_app_log(CLI_LOG_INFO, "del mac from where id < %d\n", *minId);          
    sprintf(phoneInfoCtr.sqlCmd, "DELETE FROM MAC_INFO WHERE ID <= %d;", *minId);
    rc = sqlite3_exec(RecordDb, phoneInfoCtr.sqlCmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "dele phone Info failed cmd is %s failedCode %d" , phoneInfoCtr.sqlCmd, rc);
    } 
    if (strcmp(dev_conf.dev.protocol, BAIZHUO) != 0)
    {
        fclose(fd);
    }
    if (*minId >= *maxId)
    {
        *minId = *maxId = 0;
        rmDbAndinitSqlite();
    }
    return true;

}


void sendImsiByUdp(I32 *minId, I32 *maxId)
{
    if (*maxId == 0)
    {
        return;
    }
    if ( RecordDb == NULL )
    {
        cli_app_log(CLI_LOG_ERROR, "UNexpected error in poendb\n");  
        initsqlite();
    }

    I32 getId = 0, currentId = 0;
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    getId = *minId + 1000;
    sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM PHONE_INFO WHERE ID >= %d AND ID < %d;", *minId, getId);
    I32 rc = sqlite3_get_table(RecordDb, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return ;
    }    
    I32 recordNum = 0;
    I8 nchar[1] = {'\0'};
    if ( phoneInfoCtr.nRow > 1000)
    {
        recordNum = 1000;
        cli_app_log(CLI_LOG_WARNING, "get phone record is bigger than need \n");
    }
    else
    {
        recordNum = phoneInfoCtr.nRow;
    }
    I32 i = 0 ;
    I32 index = phoneInfoCtr.nCol;
    I8 **dbResult = NULL;
    I32 id = 0;
    U32 uptime = 0;
    char szbuf[64] = {0};
    for ( i = 0; i < recordNum; i++ )
    {
        dbResult = &phoneInfoCtr.pResult[index];
        index += phoneInfoCtr.nCol;
        if ( dbResult[1] == NULL || dbResult[4] == NULL || dbResult[0] == NULL || dbResult[2] == NULL)        
        {
           cli_app_log(CLI_LOG_WARNING, "record  uptime or slotId is NULL \n");
           continue;
        }
        uptime = atoi(dbResult[1]);
        memset(szbuf, 0, 64);
        strftime(szbuf, 64, "%Y-%m-%d %H:%M:%S", localtime(((time_t *)(&uptime))));
        sendDataByUdp(dbResult[2], dbResult[3], szbuf);
        id = atoi(dbResult[0]);
        cli_app_log(CLI_LOG_DEFAULT, "get record  id is %d imsi is %s  time %s\n", atoi(dbResult[0]), dbResult[2], szbuf);
    }

    if ( phoneInfoCtr.pResult != NULL )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    if (id > 0)
    {
        *minId = id + 1;
    }
    else
    {
        *minId = getId;
    }

    cli_app_log(CLI_LOG_INFO, "del imsi from where id < %d\n", *minId);          
    sprintf(phoneInfoCtr.sqlCmd, "DELETE FROM PHONE_INFO WHERE ID <= %d;", *minId);
    rc = sqlite3_exec(RecordDb, phoneInfoCtr.sqlCmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "dele phone Info failed cmd is %s failedCode %d" , phoneInfoCtr.sqlCmd, rc);
    } 
    if (*minId >= *maxId)
    {
        *minId = *maxId = 0;
        rmDbAndinitSqlite();
    }
    return;



}


void saveUserRecord(char *imsiList, char* userName, char *userId)
{
    time_t time1;
    time(&time1);
    struct tm thistm = {0};
    localtime_r((time_t *)(&time1), &thistm);
    char day[12] = {0};
    sprintf(day, "%04d%02d%02d", 1900+thistm.tm_year,1+thistm.tm_mon,thistm.tm_mday);
    phoneInfoCtr.currentTm = time1;
    int rc = 0; 
    if (strcmp(day, phoneInfoCtr.date) != 0 || currentDB == NULL)
    {
        strcpy(phoneInfoCtr.date, day);
        char recordDb[128] = {0};
        sprintf(recordDb, "%s/%s", dbPath, day);
        rc = sqlite3_open(recordDb, &currentDB );
        if (rc != SQLITE_OK)
        {
            cli_app_log(CLI_LOG_ERROR, "can not open db %s \n", recordDb);
            return;
        }
        else
        {
            cli_app_log(CLI_LOG_NOTICE, "open db %s success\n", recordDb);
        }
        rc = sqlite3_exec(currentDB, sql_creatUserdb, 0, 0, NULL);
        if ( SQLITE_OK != rc )
        {
            cli_app_log(CLI_LOG_ERROR, "can not create table  %d \n", rc);
            sqlite3_close(currentDB);
            return ;
        }

        rc = sqlite3_exec(currentDB, sql_creatUseridx, 0, 0, NULL);
        if ( SQLITE_OK != rc )
        {
            cli_app_log(CLI_LOG_ERROR, "can not create idx %d \n", rc);
            sqlite3_close(currentDB );
            return;
        }      
    }
    char cmd[2048] = {0};
    sprintf(cmd, "INSERT INTO USER_INFO VALUES(%lu, '%s', '%s', '%s', '%s');", 
            time1, imsiList, userName, userId, "");
    rc = sqlite3_exec(currentDB, cmd, 0, 0, NULL);
    if ( rc != SQLITE_OK )
    {
        cli_app_log(CLI_LOG_ERROR, "Insert phone Info failed cmd is %s failedCode %d" , cmd, rc);
        return ;
    }
    
}


void updateRecord(char *imsi)
{
    memset(phoneInfoCtr.sqlCmd, 0, 512);
    sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM USER_INFO WHERE TIMESTAMP == %d;", phoneInfoCtr.currentTm);
    I32 rc = sqlite3_get_table(currentDB, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
    if ( rc != SQLITE_OK )
    {
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
        cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
        return;
    }
    char **dbResult = NULL;
    char buf[256] = {0};
    if (phoneInfoCtr.nRow > 0)
    {
        dbResult = &phoneInfoCtr.pResult[phoneInfoCtr.nCol];
        if (dbResult[4] != NULL)
        {
            if (strlen(dbResult[4]) > 4)
            {
                sprintf(buf, "%s;%s", dbResult[4], imsi);
            }
            else
            {
                sprintf(buf, "%s", imsi);
            }
        }
        memset(phoneInfoCtr.sqlCmd, 0, 512);
        sprintf(phoneInfoCtr.sqlCmd, "UPDATE USER_INFO SET CATCHIMSI = '%s' WHERE TIMESTAMP = %d;", buf, phoneInfoCtr.currentTm);
        rc = sqlite3_exec(currentDB, phoneInfoCtr.sqlCmd, 0, 0, NULL);
        if ( rc != SQLITE_OK )
        {
            cli_app_log(CLI_LOG_ERROR, "update phone Info failed cmd is %s failedCode %d\n" , phoneInfoCtr.sqlCmd, rc);
        }

    }
    sqlite3_free_table(phoneInfoCtr.pResult);
    phoneInfoCtr.pResult = NULL;
}


void packrecordInStruct(U32 *currentTm, U32 *startTm, U32 endTm, USERRECORD *user)
{
    struct tm thistm = {0};
    char day[12] = {0};
    char buf[128] = {0};
    sqlite3* db = NULL;
    I32 rc = 0; 
    while(*currentTm < endTm && user->num < MAX_USER_RECORD)
    {
        memset(day, 0, 12);
        memset(buf, 0, 128);
        localtime_r((time_t *)(startTm), &thistm);
        sprintf(day, "%04d%02d%02d", 1900+thistm.tm_year,1+thistm.tm_mon,thistm.tm_mday);
        sprintf(buf, "%s/%s", dbPath, day);
        if (access(buf, F_OK) != 0)
        {
            *startTm += 86400;
            *currentTm = *startTm;
            continue;
        }
        rc = sqlite3_open(buf, &db);
        if (rc != SQLITE_OK)
        {
            cli_app_log(CLI_LOG_ERROR, "can not open db %s \n", buf);
            return;
        }
        else
        {
            cli_app_log(CLI_LOG_NOTICE, "open db %s success\n", buf);
        }

        memset(phoneInfoCtr.sqlCmd, 0, 512);
        sprintf(phoneInfoCtr.sqlCmd, "SELECT * FROM USER_INFO WHERE TIMESTAMP > %d;", *currentTm);
        rc = sqlite3_get_table(db, phoneInfoCtr.sqlCmd, &phoneInfoCtr.pResult, &phoneInfoCtr.nRow, &phoneInfoCtr.nCol, NULL);
        if ( rc != SQLITE_OK )
        {
            sqlite3_free_table(phoneInfoCtr.pResult);
            phoneInfoCtr.pResult = NULL;
            cli_app_log(CLI_LOG_ERROR, "get phone info failed %x \n", rc);
            return;
        }
        char **dbResult = NULL;
        int index = phoneInfoCtr.nCol, i = 0;

        if (phoneInfoCtr.nRow > 0)
        {
            for (i = 0; i < phoneInfoCtr.nRow; i++)
            {
                if (user->num == MAX_USER_RECORD)
                    break;
                dbResult = &phoneInfoCtr.pResult[index];
                index += phoneInfoCtr.nCol;
                if (dbResult[4] == NULL || dbResult[3] == NULL || dbResult[2] == NULL ||
                        dbResult[1] == NULL || dbResult[0] == NULL)
                {
                    cli_app_log(CLI_LOG_WARNING, "record is not complete \n");
                    continue;
                }
                user->userTm[user->num] = atoi(dbResult[0]);
                *currentTm = user->userTm[user->num];
                strcpy(user->targetImsi[user->num], dbResult[1]);
                strcpy(user->userName[user->num], dbResult[2]);
                strcpy(user->userId[user->num], dbResult[3]);
                strcpy(user->catchImsi[user->num++], dbResult[4]);
                cli_app_log(CLI_LOG_INFO, "%s  %s  %s  %s  %s \n", dbResult[0], dbResult[1], dbResult[2], dbResult[3], dbResult[4]);
            }
            if (i == phoneInfoCtr.nRow)
            {
                *startTm += 86400;
                *currentTm = *startTm;
            }
        }
        sqlite3_free_table(phoneInfoCtr.pResult);
        phoneInfoCtr.pResult = NULL;
    }

}















